package com.skivingcloud.report.datas.service.impl;

import com.skivingcloud.admin.sys.entity.User;
import com.skivingcloud.common.exception.BusinessException;
import com.skivingcloud.common.utils.PageUtils;
import com.skivingcloud.common.utils.UuidUtil;
import com.skivingcloud.report.contants.JdbcConstants;
import com.skivingcloud.report.contants.UrlConstant;
import com.skivingcloud.report.datas.entity.ReportDataSource;
import com.skivingcloud.report.datas.entity.ReportDataset;
import com.skivingcloud.report.datas.mapper.ReportDataSourceMapper;
import com.skivingcloud.report.datas.service.IJdbcService;
import com.skivingcloud.report.datas.service.IReportDataSourceService;
import com.skivingcloud.report.datas.service.IReportDatasetParamService;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.client.RestClientException;
import org.springframework.web.client.RestTemplate;

/**
 * <p>
 * 数据源管理 服务实现类
 * </p>
 *
 * @author skivingcloud
 * @since 2022-07-20
 */
@Service
public class ReportDataSourceServiceImpl extends ServiceImpl<ReportDataSourceMapper, ReportDataSource> implements IReportDataSourceService {

    private static Logger log = LoggerFactory.getLogger(ReportDataSourceServiceImpl.class);

    @Resource(name = "restTemplateOuter")
	private RestTemplate restTemplateOuter;
    @Autowired
	private IReportDatasetParamService reportDatasetParamService;
    @Autowired
    private IJdbcService jdbcService;
    
	@Override
	public long total(ReportDataSource dataSource, ReportDataset dataSetDto) throws Exception {
		//区分数据类型
        String sourceType = dataSource.getSourceType();
        switch (sourceType) {
            case JdbcConstants.ELASTIC_SEARCH_SQL:
                return 0;
            case JdbcConstants.MYSQL:
                return mysqlTotal(dataSource, dataSetDto);
			case JdbcConstants.POSTGRESQL:
                return postgresqlTotal(dataSource, dataSetDto);
            default:
                throw new BusinessException("未实现的数据源模式");
        }
	}

    private long mysqlTotal(ReportDataSource dataSource, ReportDataset dataSetDto) throws Exception {
		String dynSentence = dataSource.getDynSentence();
        String sql = "select count(1) as count from (" + dynSentence + ") as gaeaExecute";
        dataSource.setDynSentence(sql);
        List<JSONObject> result = execute(dataSource);

        //sql 拼接 limit 分页信息
        int pageNumber = Integer.parseInt(dataSetDto.getContextData().getOrDefault("pageNumber", "1").toString());
        int pageSize = Integer.parseInt(dataSetDto.getContextData().getOrDefault("pageSize", "10").toString());
        String sqlLimit = " limit " + (pageNumber - 1) * pageSize + "," + pageSize;
        dataSource.setDynSentence(dynSentence.concat(sqlLimit));
        // log.info("当前total：{}, 添加分页参数,sql语句：{}", JSONObject.fromObject(result), dataSource.getDynSentence());
        return result.get(0).getLong("count");
	}

    private long postgresqlTotal(ReportDataSource dataSource, ReportDataset dataSetDto) throws Exception {
        String dynSentence = dataSource.getDynSentence();
        String sql = "select count(1) as count from (" + dynSentence + ") as gaeaExecute";
        dataSource.setDynSentence(sql);
        List<JSONObject> result = execute(dataSource);

        //sql 拼接 limit 分页信息
        int pageNumber = Integer.parseInt(dataSetDto.getContextData().getOrDefault("pageNumber", "1").toString());
        int pageSize = Integer.parseInt(dataSetDto.getContextData().getOrDefault("pageSize", "10").toString());
        String sqlLimit = " limit " + pageSize + " offset " + (pageNumber - 1) * pageSize;
        dataSource.setDynSentence(dynSentence.concat(sqlLimit));
        return result.get(0).getLong("count");
    }

	@Override
	public List<JSONObject> execute(ReportDataSource dataSource) throws Exception {
		String sourceType = dataSource.getSourceType();
        switch (sourceType) {
            case JdbcConstants.ELASTIC_SEARCH_SQL:
                return executeElasticsearchSql(dataSource);
            case JdbcConstants.MYSQL:
            case JdbcConstants.KUDU_IMAPLA:
            case JdbcConstants.ORACLE:
            case JdbcConstants.SQL_SERVER:
            case JdbcConstants.JDBC:
            case JdbcConstants.POSTGRESQL:
                return executeRelationalDb(dataSource);
            case JdbcConstants.HTTP:
                return executeHttp(dataSource);
            default:
			throw new BusinessException("未实现的数据源模式");
        }
	}

   /**
    * es数据集查询
    * @param dto
    * @return
    */
    public List<JSONObject> executeElasticsearchSql(ReportDataSource dto) {
        analysisHttpConfig(dto);
        HttpHeaders headers = new HttpHeaders();
        headers.setAll(JSONObject.fromObject(dto.getHeader()));
        HttpEntity<String> entity = new HttpEntity<>(dto.getDynSentence(), headers);
        ResponseEntity<JSONObject> exchange;
        try {
            exchange = restTemplateOuter.exchange(dto.getApiUrl(), HttpMethod.valueOf(dto.getMethod()), entity, JSONObject.class);
        } catch (Exception e) {
            log.error("error",e);
            throw e;
        }
        if (exchange.getStatusCode().isError()) {
            throw new BusinessException(exchange.getBody().toString());
        }
        List<JSONObject> result;
        try {
            JSONObject body = exchange.getBody();
            //解析es sql数据
            if (null == body) {
                return null;
            }
            JSONArray columns = body.getJSONArray("columns");
            JSONArray rows = body.getJSONArray("rows");
            result = new ArrayList<>();
            for (int i = 0; i < rows.size(); i++) {
                JSONArray row = rows.getJSONArray(i);
                JSONObject jsonObject = new JSONObject();
                for (int j = 0; j < row.size(); j++) {
                    String name = columns.getJSONObject(j).getString("name");
                    String value = row.getString(j);
                    jsonObject.put(name, value);
                }
                result.add(jsonObject);
            }
        } catch (Exception e) {
            log.error("error",e);
            throw e;
        }
        return result;
    }

	/**
     * es通过api获取数据
     *
     * @param dto
     * @return
     */
    public void analysisHttpConfig(ReportDataSource dto) {
        JsonNode json = dto.getSourceConfig();
		if(!json.hasNonNull("apiUrl")){
			throw new BusinessException("参数为空");
		}
		if(!json.hasNonNull("method")){
			throw new BusinessException("参数为空");
		}
		if(!json.hasNonNull("header")){
			throw new BusinessException("参数为空");
		}
		if(!json.hasNonNull("body")){
			throw new BusinessException("参数为空");
		}
        String apiUrl = json.get("apiUrl").asText();
        String method = json.get("method").asText();
        String header = json.get("header").asText();
        String body = json.get("body").asText();
        //解决url中存在的动态参数
        apiUrl = reportDatasetParamService.transform(dto.getContextData(), apiUrl);
        //请求头中动态参数
        header = reportDatasetParamService.transform(dto.getContextData(), header);
        dto.setApiUrl(apiUrl);
        dto.setMethod(method);
        dto.setHeader(header);
        dto.setBody(body);
    }

    /**
     * 关系型数据库查询
     * @param dataSource
     * @return
     */
    private List<JSONObject> executeRelationalDb(ReportDataSource dataSource) throws Exception{
        analysisRelationalDbConfig(dataSource);
        Connection pooledConnection = null;
        try {
            pooledConnection = jdbcService.getPooledConnection(dataSource);

            PreparedStatement statement = pooledConnection.prepareStatement(dataSource.getDynSentence());
            ResultSet rs = statement.executeQuery();

            int columnCount = rs.getMetaData().getColumnCount();

            List<String> columns = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rs.getMetaData().getColumnLabel(i);
                columns.add(columnName);
            }
            List<JSONObject> list = new ArrayList<>();
            while (rs.next()) {
                JSONObject jo = new JSONObject();
                columns.forEach(t -> {
                    try {
                        Object value = rs.getObject(t);
                        //数据类型转换
                        Object result = dealResult(value);
                        jo.put(t, result);
                    } catch (SQLException throwable) {
                        log.error("error",throwable);
                        // throw throwable;
                    }
                });
                list.add(jo);
            }
            return list;
        } catch (Exception throwable) {
            log.error("error",throwable);
            throw throwable;
        } finally {
            try {
                if (pooledConnection != null) {
                    pooledConnection.close();
                }
            } catch (SQLException throwable) {
                log.error("error",throwable);
                throw throwable;
            }
        }
    }

    public void analysisRelationalDbConfig(ReportDataSource dto) {
        JsonNode  json = dto.getSourceConfig();
        if(!json.hasNonNull("jdbcUrl")){
			throw new BusinessException("jdbcUrl参数为空");
		}
		if(!json.hasNonNull("driverName")){
			throw new BusinessException("driverName参数为空");
		}
        String jdbcUrl = json.get("jdbcUrl").asText();
        String username = json.get("username").asText();
        String password = json.get("password").asText();
        String driverName = json.get("driverName").asText();
        dto.setJdbcUrl(jdbcUrl);
        dto.setDriverName(driverName);
        dto.setUsername(username);
        dto.setPassword(password);
    }

    /**
     * 解决sql返回值 类型问题
     * (through reference chain: java.util.HashMap["pageData"]->java.util.ArrayList[0]->java.util.HashMap["UPDATE_TIME"]->oracle.sql.TIMESTAMP["stream"])
     * @param result
     * @return
     * @throws SQLException
     */
    private Object dealResult(Object result) throws SQLException {
        if (null == result) {
            return result;
        }
        String type = result.getClass().getName();
        if ("oracle.sql.TIMESTAMP".equals(type)) {
            //oracle.sql.TIMESTAMP处理逻辑
            //不想支持oracle了，用到的时候再引入oracle的jdbc驱动吧
            // return new Date(((oracle.sql.TIMESTAMP) result).getTime());
            return new Date();
        }

        return result;
    }

    /**
     * http 执行获取数据
     *
     * @param dto
     */
    public List<JSONObject> executeHttp(ReportDataSource dto) {
        analysisHttpConfig(dto);
        HttpHeaders headers = new HttpHeaders();
        headers.setAll(JSONObject.fromObject(dto.getHeader()));
        HttpEntity<String> entity = new HttpEntity<>(dto.getDynSentence(), headers);
        ResponseEntity<Object> exchange;
        try {
            exchange = restTemplateOuter.exchange(dto.getApiUrl(), HttpMethod.valueOf(dto.getMethod()), entity, Object.class);
        } catch (Exception e) {
            log.error("error",e);
            throw e;
        }
        if (exchange.getStatusCode().isError()) {
            throw new BusinessException(exchange.getBody().toString());
        }
        Object body = exchange.getBody();
        String jsonStr = JSONObject.fromObject(body).toString();
        List<JSONObject> result = new ArrayList<>();
        if (jsonStr.trim().startsWith(UrlConstant.LEFT_BIG_BOAST) && jsonStr.trim().endsWith(UrlConstant.RIGTH_BIG_BOAST)) {
            //JSONObject
            result.add(JSONObject.fromObject(jsonStr));
        } else if (jsonStr.trim().startsWith(UrlConstant.LEFT_MIDDLE_BOAST) && jsonStr.trim().endsWith(UrlConstant.RIGHT_MIDDLE_BOAST)) {
            //List
            result = JSONArray.fromObject(jsonStr);
        } else {
            result.add(new JSONObject());
        }
        return result;
    }

    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        PageUtils pageUtils = PageUtils.getPageInfo(params);
		Page<ReportDataSource> p = PageHelper.startPage(pageUtils.getPage(), pageUtils.getLimit());
		ReportDataSource reportDataSource = (ReportDataSource) JSONObject.toBean(
				JSONObject.fromObject(params.get("dataForm")),
				ReportDataSource.class);
		QueryWrapper<ReportDataSource> queryWrapper = new QueryWrapper<>();
		if (StringUtils.isNotBlank(reportDataSource.getSourceType())) {
			queryWrapper.eq("source_type", reportDataSource.getSourceType());
		}
		if (StringUtils.isNotBlank(reportDataSource.getSourceCode())) {
			queryWrapper.like("source_code", "%" + reportDataSource.getSourceCode() + "%");
		}
        if (StringUtils.isNotBlank(reportDataSource.getSourceName())) {
			queryWrapper.like("source_name", "%" + reportDataSource.getSourceName() + "%");
		}
		this.baseMapper.selectList(queryWrapper);
		pageUtils = new PageUtils(p);
		return pageUtils;
    }

    @Override
    public ReportDataSource selectById(String id) {
        return this.baseMapper.selectById(id);
    }

    @Override
    public void deleteBatch(String[] ids) {
        this.baseMapper.deleteBatchIds(Arrays.asList(ids));
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void add(ReportDataSource reportDataSource, User user) {
        reportDataSource.setId(UuidUtil.get32UUID());
        reportDataSource.setEnableFlag("1");
        reportDataSource.setDeleteFlag("0");
        reportDataSource.setCreater(user.getId());
        reportDataSource.setCreateTime(LocalDateTime.now());
		this.baseMapper.insert(reportDataSource);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void update(ReportDataSource reportDataSource, User user) {
        reportDataSource.setModUser(user.getId());
        reportDataSource.setModTime(LocalDateTime.now());
        this.baseMapper.updateById(reportDataSource);
    }

    @Override
    public boolean testConnection(ReportDataSource reportDataSource) throws Exception {
        switch (reportDataSource.getSourceType()) {
            case JdbcConstants.ELASTIC_SEARCH_SQL:
                testElasticsearchSqlConnection(reportDataSource);
                break;
            case JdbcConstants.MYSQL:
            case JdbcConstants.KUDU_IMAPLA:
            case JdbcConstants.ORACLE:
            case JdbcConstants.SQL_SERVER:
            case JdbcConstants.JDBC:
            case JdbcConstants.POSTGRESQL:
                testRelationalDb(reportDataSource);
                break;
            case JdbcConstants.HTTP:
                testHttp(reportDataSource);
                break;
            default:
                throw new BusinessException("未支持的数据源类型");
        }
        log.info("测试连接成功：{}", new ObjectMapper().writeValueAsString(reportDataSource));
        return true;
    }

    /**
     * Elasticsearch 测试连接
     *
     * @param reportDataSource
     */
    public void testElasticsearchSqlConnection(ReportDataSource reportDataSource) {
        analysisHttpConfig(reportDataSource);
        String apiUrl = reportDataSource.getApiUrl();
        String method = reportDataSource.getMethod();
        String body = reportDataSource.getBody();
        HttpHeaders headers = new HttpHeaders();
        headers.setAll(JSONObject.fromObject(reportDataSource.getHeader()));
        HttpEntity<String> entity = new HttpEntity<>(body, headers);
        ResponseEntity<Object> exchange;
        try {
            exchange = restTemplateOuter.exchange(apiUrl, HttpMethod.valueOf(method), entity, Object.class);
            if (exchange.getStatusCode().isError()) {
                throw new BusinessException(exchange.getBody().toString());
            }
        } catch (RestClientException e) {
            throw new BusinessException(e.getMessage(), e);
        }
    }

    /**
     * 关系型数据库 测试连接
     *
     * @param dto
     */
    public void testRelationalDb(ReportDataSource dto) {
        analysisRelationalDbConfig(dto);
        try {
            Connection unPooledConnection = jdbcService.getUnPooledConnection(dto);
            String catalog = unPooledConnection.getCatalog();
            log.info("数据库测试连接成功：{}", catalog);
            unPooledConnection.close();
        } catch (SQLException e) {
            log.error("error",e);
            if (e.getCause() instanceof ClassNotFoundException) {
                throw new BusinessException("找不到数据库驱动程序", e);
            } else {
                throw new BusinessException("测试连接失败", e);
            }

        }
    }

    /**
     * http 测试连接
     *
     * @param dto
     */
    public void testHttp(ReportDataSource dto) {
        analysisHttpConfig(dto);
        String apiUrl = dto.getApiUrl();
        String method = dto.getMethod();
        String body = dto.getBody();
        HttpHeaders headers = new HttpHeaders();
        headers.setAll(JSONObject.fromObject(dto.getHeader()));
        HttpEntity<String> entity = new HttpEntity<>(body, headers);
        ResponseEntity<Object> exchange;
        try {
            exchange = restTemplateOuter.exchange(apiUrl, HttpMethod.valueOf(method), entity, Object.class);
            if (exchange.getStatusCode().isError()) {
                throw new BusinessException(exchange.getBody().toString());
            }
        } catch (RestClientException e) {
            throw new BusinessException(e.getMessage(), e);
        }
    }

    @Override
    public List<ReportDataSource> selectAll() {
        QueryWrapper<ReportDataSource> queryWrapper = new QueryWrapper<>();
        return this.baseMapper.selectList(queryWrapper);
    }
}
